using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web;
using System.Configuration;
using net.windward.util;
using System.Web.UI;
using Microsoft.SharePoint;
using log4net;

namespace ArrowWebParts
{
    public class ScheduleViewerWebPart : WebPart
    {
        /// <summary>
        /// title & status
        /// </summary>
        private Label lblStatus;
        /// <summary>
        /// time zone label
        /// </summary>
        private Label lblTimeZone;
        /// <summary>
        /// label for schedule item list
        /// </summary>
        private Label lblItems;
        /// <summary>
        /// list of scheduled items
        /// </summary>
        private SPGridView grdItems;

        private DataView view;

        private bool isLicensed = false;
        /// <summary>
        /// the log
        /// </summary>
        private static ILog log = LogManager.GetLogger(typeof(ScheduleViewerWebPart));

        private String ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["Arrow"].ConnectionString; }
        }


        protected override void CreateChildControls()
        {
            try
            {
                base.CreateChildControls();

                if (log.IsDebugEnabled)
                    log.Debug("creating child controls");

                try
                {
                    // read in license from web.config
                    ArrowUtils.InitializeArrow(string.Concat(Page.Request.PhysicalApplicationPath, "\\web.config"));
                    isLicensed = true;
                }
                catch (LicenseException lex)
                {
                    ArrowUtils.LogFatalException(log, lex);
                    Controls.Add(new LiteralControl(string.Format("License Error: {0}", lex.Message)));
                    return;
                }

                // title/status label
                lblStatus = new Label();
                lblStatus.Text = "<h1>Scheduled Windward Reports: Ready</h1>";
                Controls.Add(lblStatus);

                // time zone label
                lblTimeZone = new Label();
                string timeZone = string.Empty;
                SPRegionalSettings userRegion = SPContext.Current.Web.CurrentUser.RegionalSettings;
                if (userRegion != null)
                    timeZone = userRegion.TimeZone.Description;
                else
                    timeZone = TimeZone.CurrentTimeZone.StandardName;
                lblTimeZone.Text = timeZone;
                Controls.Add(lblTimeZone);

                // schedule item view label
                lblItems = new Label();
                lblItems.Text = "<br/>";
                Controls.Add(lblItems);

                // schedule item grid view
                grdItems = new SPGridView();

                SPBoundField boundField = new SPBoundField();
                boundField.DataField = "scheduleId";
                boundField.Visible = false;
                grdItems.Columns.Add(boundField);

                boundField = new SPBoundField();
                boundField.HeaderText = "Title";
                boundField.DataField = "sTitle";
                boundField.SortExpression = "sTitle";
                grdItems.Columns.Add(boundField);

                boundField = new SPBoundField();
                boundField.HeaderText = "Description";
                boundField.DataField = "sDescription";
                boundField.SortExpression = "sDescription";
                grdItems.Columns.Add(boundField);

                boundField = new SPBoundField();
                boundField.HeaderText = "Last Run";
                boundField.DataField = "runLast";
                boundField.SortExpression = "runLast";
                grdItems.Columns.Add(boundField);

                boundField = new SPBoundField();
                boundField.HeaderText = "Previous Result";
                boundField.DataField = "runResult";
                boundField.SortExpression = "runResult";
                grdItems.Columns.Add(boundField);

                boundField = new SPBoundField();
                boundField.HeaderText = "Next Scheduled";
                boundField.DataField = "runNext";
                boundField.SortExpression = "runNext";
                grdItems.Columns.Add(boundField);

                grdItems.AutoGenerateColumns = false;
                grdItems.AllowSorting = true;
                grdItems.Sorting += new GridViewSortEventHandler(grdItems_Sorting);
                grdItems.AutoGenerateDeleteButton = true;
                grdItems.RowDeleting += new GridViewDeleteEventHandler(grdItems_RowDeleting);
                Controls.Add(grdItems);
            }
            catch (Exception e)
            {
                Label errorMsg = new Label();
                errorMsg.Text = "You have encountered an error: " + e.Message;
                Controls.Clear();
                Controls.Add(errorMsg);
                ArrowUtils.LogFatalException(log, e);
            }

        }

        protected override void OnPreRender(EventArgs e)
        {
            try
            {
                base.OnPreRender(e);

                if (log.IsDebugEnabled)
                    log.Debug("handling prerender");

                if (!isLicensed)
                    return;

                // data for schedule item grid view
                if (view == null)
                    loadUTCView();

                // change all the times from utc to server local
                SPUser user = SPContext.Current.Web.CurrentUser;
                foreach (DataRowView row in view)
                {
                    // run next
                    DateTime runNextUtc = ((DateTime) row["runNext"]);
                    row["runNext"] = ArrowUtils.UtcToUserTime(user, runNextUtc);

                    // run last
                    if (!(row["runLast"] is DBNull) && ((DateTime) row["runLast"]).Kind != DateTimeKind.Local)
                    {
                        DateTime runLastUtc = ((DateTime) row["runLast"]);
                        row["runLast"] = ArrowUtils.UtcToUserTime(user, runLastUtc);
                    }
                }

                grdItems.DataSource = view;
                string[] names = {"scheduleId"};
                grdItems.DataKeyNames = names;
                grdItems.DataBind();
            }
            catch(Exception OnPreRenderEx)
            {
                Label errorMsg = new Label();
                errorMsg.Text = "You have encountered an error: " + OnPreRenderEx.Message;
                Controls.Clear();
                Controls.Add(errorMsg);
                ArrowUtils.LogFatalException(log, OnPreRenderEx);
            }
        }
        /// <summary>
        /// Initializes the DataView, view, with the default, UTC-time schedule data.
        /// </summary>
        private void loadUTCView()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT scheduleId, sTitle, sDescription, runLast, runResult, runNext FROM Schedule", conn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable result = new DataTable();
            adapter.Fill(result);
            view = new DataView(result);
        }

        void grdItems_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            if (log.IsDebugEnabled)
                log.Debug("handling row delete");

            SPGridViewRow row = grdItems.Rows[e.RowIndex] as SPGridViewRow;
            Label lblSchedID = row.Cells[1].Controls[0] as Label;
            string schedID = lblSchedID.Text;
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    // get rid of any schedule vars
                    SqlCommand cmd = new SqlCommand("DELETE from ScheduleVars where scheduleId='" + schedID + "'", conn);
                    cmd.ExecuteNonQuery();
                    // get rid of the ScheduleRun items (fk constraint)
                    cmd = new SqlCommand("DELETE from ScheduleRun where scheduleId='" + schedID + "'", conn);
                    cmd.ExecuteNonQuery();
                    // get rid of this schedule item
                    cmd = new SqlCommand("DELETE from Schedule where scheduleId='" + schedID + "'", conn);
                    cmd.ExecuteNonQuery();
                }
                grdItems.DataBind();
                // refresh gridview content
                Context.Response.Redirect(Context.Request.Path);
            }
            catch (SqlException ex)
            {
                ArrowUtils.LogWarnException(log, ex);
                lblStatus.Text = "<p>You do not have permission to delete scheduled reports.</p>";
                e.Cancel = true;
            }
        }

        void grdItems_Sorting(object sender, GridViewSortEventArgs e)
        {
            if (log.IsDebugEnabled)
                log.Debug("handling sort");

            // from http://blogs.msdn.com/powlo/archive/2007/02/25/displaying-custom-data-through-sharepoint-lists-using-spgridview-and-spmenufield.aspx
            string lastExpression = "";
            if (ViewState["SortExpression"] != null)
                lastExpression = ViewState["SortExpression"].ToString();

            string lastDirection = "asc";
            if (ViewState["SortDirection"] != null)
                lastDirection = ViewState["SortDirection"].ToString();

            string newDirection = "asc";
            if (e.SortExpression == lastExpression)
                newDirection = (lastDirection == "asc") ? "desc" : "asc";

            ViewState["SortExpression"] = e.SortExpression;
            ViewState["SortDirection"] = newDirection;

            // load default view for event-handlers
            loadUTCView();
            view.Sort = e.SortExpression + " " + newDirection;
            grdItems.DataBind();
        }
    }
}
